package com.databaseconnectionmobilapi.service.mysql.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.databaseconnectionmobilapi.constant.APIConstant;
import com.databaseconnectionmobilapi.constant.Storage;
import com.databaseconnectionmobilapi.domain.dto.*;
import com.databaseconnectionmobilapi.domain.entity.*;
import com.databaseconnectionmobilapi.domain.vo.SelectAllTableVO;
import com.databaseconnectionmobilapi.domain.vo.SelectDatabaseNamesVO;
import com.databaseconnectionmobilapi.mapper.DatabaseMapper;
import com.databaseconnectionmobilapi.service.mysql.MysqlOpService;
import com.databaseconnectionmobilapi.util.result.RestResult;
import com.databaseconnectionmobilapi.util.util.DbUtil;
import lombok.AllArgsConstructor;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@AllArgsConstructor
public class MysqlOpServiceImpl extends ServiceImpl<DatabaseMapper, DatabaseEntity> implements MysqlOpService {
    private DatabaseMapper databaseMapper;

    private Boolean execute(Integer id, String sql) throws SQLException {
        Connection connection = establishMySQLConnection(id);
        Statement statement = null;
        try {
            statement = ((Connection) Storage.CONNECTION_STORAGE.get(id)).createStatement();
            return statement.execute(sql);
        } finally {
            if (statement != null) {
                statement.close();
                connection.close();
            }
        }
    }
    
    @Override
    public Connection establishMySQLConnection(Integer id) {
        DatabaseEntity databaseEntity = databaseMapper.selectById(id);

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(
                    "jdbc:mysql://" + databaseEntity.getIp() + ":" + databaseEntity.getPort(),
                    databaseEntity.getAccount(), databaseEntity.getPassword());
            Storage.CONNECTION_STORAGE.put(id, connection);
            Storage.CONNECTIONS++;
            return connection;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return null;
    }

    @Override
    public RestResult selectDatabaseNames(Integer id) {
        List<SelectDatabaseNamesVO> selectDatabaseNamesVOS = new ArrayList<>();
        Statement statement = null;
        Statement statement1 = null;
        Connection connection = establishMySQLConnection(id);
        try {
            statement = connection.createStatement();
            statement1 = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("show databases");
            while (resultSet.next()) {
                String name = resultSet.getString(1);
                ResultSet databaseInfo = statement1.executeQuery(" SELECT * FROM information_schema.SCHEMATA WHERE schema_name = '" + name + "'");
                while (databaseInfo.next()) {
                    selectDatabaseNamesVOS.add(SelectDatabaseNamesVO
                            .builder()
                            .name(name)
                            .charset(databaseInfo.getString(3))
                            .collation(databaseInfo.getString(4))
                            .build());
                }
                databaseInfo.close();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement1.close();
                    statement.close();
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return APIConstant.REST_RESULT_BUILDER.success(selectDatabaseNamesVOS);
    }

    @Override
    public RestResult selectAllCharset(Integer id) {
        List<CharsetEntity> charsetEntities = new ArrayList<>();

        Connection connection = establishMySQLConnection(id);
        Statement statement = null;
        try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SHOW CHARACTER SET");

            // 取出所有字段名
            while (resultSet.next()) {
                charsetEntities.add(CharsetEntity
                        .builder()
                        .charset(resultSet.getString("Charset"))
                        .defaultCollation(resultSet.getString("Default collation"))
                        .value(resultSet.getString("Charset"))
                        .text(resultSet.getString("Charset"))
                        .build());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return APIConstant.REST_RESULT_BUILDER.success(charsetEntities);
    }

    @Override
    public RestResult selectAllCollation(SelectAllCollationDTO selectAllCollationDTO) {
        List<CollationEntity> collationEntities = new ArrayList<>();
        Connection connection = establishMySQLConnection(selectAllCollationDTO.getId());;
        Statement statement = null;
        try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SHOW COLLATION LIKE '" + selectAllCollationDTO.getCharset() + "%'");

            // 取出所有字段名
            while (resultSet.next()) {
                collationEntities.add(CollationEntity
                        .builder()
                        .value(resultSet.getString("Collation"))
                        .text(resultSet.getString("Collation"))
                        .collation(resultSet.getString("Collation"))
                        .build());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return APIConstant.REST_RESULT_BUILDER.success(collationEntities);
    }

    @Override
    public RestResult addMysqlDatabase(AddMysqlDatabaseDTO addMysqlDatabase) throws SQLException {
        execute(addMysqlDatabase.getId(),
                "CREATE DATABASE `" + addMysqlDatabase.getName() +
                        "` CHARACTER SET " + addMysqlDatabase.getCharset() +
                        " COLLATE " + addMysqlDatabase.getCollation());
        return APIConstant.REST_RESULT_BUILDER.success();
    }

    @Override
    public RestResult delMysqlDatabase(DelMysqlDatabaseDTO delMysqlDatabaseDTO) throws SQLException {
        DbUtil.execute(delMysqlDatabaseDTO.getId(), "drop database `" + delMysqlDatabaseDTO.getName() + "`");

        return APIConstant.REST_RESULT_BUILDER.success();
    }

    @Override
    public RestResult updateMysqlDatabase(UpdateMysqlDatabaseDTO updateMysqlDatabaseDTO) throws SQLException {
        DbUtil.execute(updateMysqlDatabaseDTO.getId(),
                "ALTER DATABASE `" + updateMysqlDatabaseDTO.getName() +
                        "` CHARACTER SET " + updateMysqlDatabaseDTO.getCharset() +
                        " COLLATE " + updateMysqlDatabaseDTO.getCollation());

        return APIConstant.REST_RESULT_BUILDER.success();
    }

    @Override
    public RestResult selectAllTable(SelectAllTableDTO selectAllTableDTO) {
        List<SelectAllTableVO> selectAllTableVOS = new ArrayList<>();
        Connection connection = establishMySQLConnection(selectAllTableDTO.getId());
        Statement statement = null;
        try {
            statement = connection.createStatement();
            statement.execute("use " + selectAllTableDTO.getName());
            ResultSet resultSet = statement.executeQuery("show tables");
            while (resultSet.next()) {
                selectAllTableVOS.add(
                        SelectAllTableVO
                                .builder()
                                .name(resultSet.getString(1))
                                .build());
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return APIConstant.REST_RESULT_BUILDER.success(selectAllTableVOS);
    }

    @Override
    public RestResult addTable(AddTableDTO addTableDTOS) {
        try {
            String sql = "create table `" + addTableDTOS.getTableName() + "` ( ";

            for (TableDTO tableDTO : addTableDTOS.getTableDTOS()) {
                String field = "`" + tableDTO.getName() + "` " + tableDTO.getType();
                field += "(" + tableDTO.getLength() + ") ";
//                field += !fieldEntity.getIsNull() ? " not null " : " ";
//                field += fieldEntity.getDefaultValue() != null ? !fieldEntity.getDefaultValue().matches("int|long") ?
//                        " default '" + fieldEntity.getDefaultValue() + "'" : " default " + fieldEntity.getDefaultValue() : " ";
                field += " comment '" + tableDTO.getComment() + "'";
                field += tableDTO.getKey() == 0 ? " AUTO_INCREMENT, PRIMARY KEY (`" + tableDTO.getName() + "`)" : " ";
                field += ",";

                sql += field;
            }

            sql = sql.substring(0, sql.length() - 1);

            sql += ")ENGINE=InnoDB DEFAULT CHARSET=utf8";
            DbUtil.execute(addTableDTOS.getId(), sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return APIConstant.REST_RESULT_BUILDER.success();
    }

    @Override
    public RestResult updateTable(UpdateTableDTO updateTableDTO) throws SQLException {
        for (TableColumnDTO tableColumnDTO : updateTableDTO.getTableColumnDTOS()) {
            String sql = "ALTER TABLE `" + updateTableDTO.getTableName() + "`";
            String temp;
            switch (tableColumnDTO.getOp()) {
                case 1:
                    temp = sql + " CHANGE `" + tableColumnDTO.getOldName() + "` " +
                            "`" + tableColumnDTO.getName() + "` " + tableColumnDTO.getType() +
                            "(" + tableColumnDTO.getLength() + ") comment '" + tableColumnDTO.getComment() + "'";

                    if (tableColumnDTO.getKey() == 0) {
                        temp += " NOT NULL";
                    }

                    DbUtil.execute(updateTableDTO.getId(), temp);

                    if (!tableColumnDTO.getKey().equals(tableColumnDTO.getOldKey())) {
                        if (tableColumnDTO.getKey() == 0) {
                            DbUtil.execute(updateTableDTO.getId(),
                                    sql + " ADD PRIMARY KEY (`" + tableColumnDTO.getOldName() + "`)");
                        } else if (tableColumnDTO.getKey() == 1) {
                            DbUtil.execute(updateTableDTO.getId(),
                                    sql + " drop primary key");
                        }
                    }

                    break;
                case 0:
                    temp = sql + " ADD `" + tableColumnDTO.getOldName() + "` " + tableColumnDTO.getType() +
                            "(" + tableColumnDTO.getLength() + ") comment '" + tableColumnDTO.getComment() + "'";

                    if (tableColumnDTO.getKey() == 0) {
                        temp += " NOT NULL";
                    }

                    DbUtil.execute(updateTableDTO.getId(), temp);

                    if (tableColumnDTO.getOldKey() == 0) {
                        DbUtil.execute(updateTableDTO.getId(),
                                sql + " ADD PRIMARY KEY (`" + tableColumnDTO.getOldName() + "`)");
                    }
                    break;
                case 2:

                    if (tableColumnDTO.getOldKey() == 0) {
                        DbUtil.execute(updateTableDTO.getId(),
                                sql + " drop primary key");
                    }

                    DbUtil.execute(updateTableDTO.getId(),
                            sql + " DROP COLUMN `" + tableColumnDTO.getOldName() + "`");

                    break;
            }
        }

        if (!updateTableDTO.getNewTableName().equals(updateTableDTO.getTableName())) {
            DbUtil.execute(updateTableDTO.getId(),
                    "RENAME " + updateTableDTO.getName() + " " +
                            updateTableDTO.getTableName() + " TO " + updateTableDTO.getNewTableName());
        }

        return APIConstant.REST_RESULT_BUILDER.success();
    }

    @Override
    public RestResult delTable(DelTableDTO delTableDTO) throws SQLException {
        DbUtil.execute(delTableDTO.getId(),
                " DROP TABLE `" + delTableDTO.getName() + "`");

        return APIConstant.REST_RESULT_BUILDER.success();
    }

    @Override
    public RestResult selectTableStruct(SelectTableStructDTO selectTableStructDTO) {
        Connection connection = establishMySQLConnection(selectTableStructDTO.getId());
        Statement statement = null;
        List<FieldEntity> list = new ArrayList<>();
        try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(
                    "SELECT COLUMN_NAME, COLUMN_TYPE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT, COLUMN_KEY" +
                            " FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = '" + selectTableStructDTO.getName() +
                            "' AND table_name = '" + selectTableStructDTO.getTableName() +
                            "' ORDER BY TABLE_NAME, ORDINAL_POSITION;");

            while (resultSet.next()) {
                list.add(FieldEntity
                        .builder()
//                        .key(key++)
                        .name(resultSet.getString("COLUMN_NAME"))
                        .type(resultSet.getString("DATA_TYPE"))
//                        .isNull(resultSet.getBoolean("IS_NULLABLE"))
                        .comment(resultSet.getString("COLUMN_COMMENT"))
                        .length(resultSet.getLong("CHARACTER_MAXIMUM_LENGTH"))
                        .key(resultSet.getString("COLUMN_KEY").equals("PRI") ? 0 : 1)
//                        .defaultValue(resultSet.getString("COLUMN_DEFAULT"))
                        .edit(true)
                        .build());
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return APIConstant.REST_RESULT_BUILDER.success(list);
    }

    @Override
    public RestResult selectData(SelectDataDTO selectDataDTO) {
        List<Map<String, Object>> tableData = new ArrayList<>();
        List<FieldEntity> fieldEntities = new ArrayList<>();
        List<List<DataEntity>> dataList = new ArrayList<>();

        Connection connection = establishMySQLConnection(selectDataDTO.getId());
        Statement statement = null;
        try {
            statement = connection.createStatement();

            ResultSet columnSet = statement.executeQuery("desc " + selectDataDTO.getTableName());

            // 取出所有字段名
            while (columnSet.next()) {
                fieldEntities.add(FieldEntity
                        .builder()
                        .name(columnSet.getString(1))
                        .type(columnSet.getString(2))
                        .build());
            }

            columnSet.close();

            ResultSet resultSet = statement.executeQuery(
                    "select * from " + selectDataDTO.getTableName() +
                            " limit " + selectDataDTO.getPage() + ", " + selectDataDTO.getPageSize());

            while (resultSet.next()) {
                List<DataEntity> dataEntities = new ArrayList<>();
                for (int i = 1; i <= fieldEntities.size(); i++) {
                    DataEntity dataEntity = DataEntity
                            .builder()
                            .value(resultSet.getObject(i))
                            .build();
                    dataEntities.add(dataEntity);
                }

                dataList.add(dataEntities);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        List<Object> list = new ArrayList<>();
        list.add(fieldEntities);
        list.add(dataList);

        return APIConstant.REST_RESULT_BUILDER.success(list);
    }
}
